﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.Common;
using System.Data;
using System.Data.SqlServerCe;
using SD.LLBLGen.Pro.ORMSupportClasses;

using VNet.VisiSuite.Common.Logger;
using System.Globalization;

namespace VNet.VisiSuite.VisiBoard.BusinessLogic
{
    public class VersionUtil
    {
        private const string APP_DB_VERSION = "2.0.4";
        private const string connectionKeyString = "ConnectionString.SQL Server (SqlClient)";

        public SqlCeConnection conn;
        public SqlCeCommand command;
        public SqlCeDataAdapter adapter;


        public void CheckExistingData()
        {

            if (!DatabaseVersionExists())
            {
                CreateDatabaseVersionTable();
            }

            string db_ver = GetDBVersion();
            int diff = CompareDBVersion(APP_DB_VERSION, db_ver);

            fixCellOperator();

            if (diff == -1)
            {
                return;
            }
            else if (diff == 1)
            {
                if (db_ver == "2.0.2")
                {
                    updateTo2_0_3();
                    updateTo2_0_4();
                }
                else if (db_ver == "2.0.3")
                {
                    updateTo2_0_4();
                }
                else
                {
                    string msg = "Unsupported database version - " + db_ver;
                    FileLog.Fatal(msg);
                    throw new Exception(msg);
                }
                
            }
        }

        public bool DatabaseVersionExists()
        {
            bool ret = false;
            CreateCommand("SELECT COUNT(*) AS [COUNT] FROM DatabaseVersion");

            if (conn.State != ConnectionState.Open)
                conn.Open();

            DbDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                if (reader.GetInt32(0) > 0)
                {
                    ret = true;
                }
                else
                {
                    command.CommandText = "INSERT INTO DatabaseVersion(VersionNumber, CreatedDate) VALUES('2.0.0', GetDate())";
                    command.ExecuteNonQuery();
                    ret = true;
                }
            }
            reader.Close();
            CloseConnection();
            return ret;
        }

        public bool CreateDatabaseVersionTable()
        {
            bool ret = false;
            string createQuery = "";
            createQuery += "CREATE TABLE [DatabaseVersion] (";
            createQuery += "[DatabaseVersionID] INTEGER IDENTITY(1,1) NOT NULL,";
            createQuery += "[VersionNumber] NVARCHAR(10),";
            createQuery += "[CreatedDate] Datetime,";
            createQuery += "CONSTRAINT [PK_DatabaseVersion] PRIMARY KEY ([DatabaseVersionID])";
            createQuery += ")";
            try
            {
                CreateCommand(createQuery);

                if (conn.State != ConnectionState.Open)
                    conn.Open();

                command.ExecuteNonQuery();
                command.CommandText = "INSERT INTO DatabaseVersion(VersionNumber, CreatedDate) VALUES('1.0.0', GetDate())";
                command.ExecuteNonQuery();
                ret = true;
            }
            catch (Exception ex)
            {
                string msg = ex.Message;
                Console.Out.WriteLine(ex.Message);
                ret = false;
            }
            CloseConnection();
            return ret;
        }

        public string GetDBVersion()
        {
            string selectQuery = "";
            string ret = "";
            selectQuery = "SELECT TOP 1 VersionNumber FROM DatabaseVersion ORDER BY DatabaseVersionID DESC";
            try
            {
                CreateCommand(selectQuery);

                if (conn.State != ConnectionState.Open)
                    conn.Open();

                DbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    ret = reader.GetString(0);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ret = "";
                Console.Out.WriteLine(ex.Message);
            }
            CloseConnection();
            return ret;
        }

        private void updateTo2_0_3()
        {
            var query = new[] {
                "ALTER TABLE AreaShift ADD Hidden BIT DEFAULT 0 NOT NULL",
                "CREATE TABLE [OperatorAuth] ([OperatorAuthId] INTEGER IDENTITY(1,1) NOT NULL, [OperatorID] INTEGER NOT NULL, [WorkOrderField1] NVARCHAR(20), PRIMARY KEY ([OperatorAuthId]))",
                "CREATE  INDEX [IDX_OperatorAuth_WorkOrderField1] ON [OperatorAuth] ([WorkOrderField1])",
                "CREATE  INDEX [IDX_OperatorAuth_2_FK] ON [OperatorAuth] ([OperatorID])",
                "CREATE UNIQUE  INDEX [IDX_OperatorAuth_PK] ON [OperatorAuth] ([OperatorAuthId])",
                "INSERT INTO DatabaseVersion(VersionNumber, CreatedDate) VALUES('2.0.3', GetDate())"
            };

            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                foreach (string s in query)
                {
                    try
                    {
                        CreateCommand(s);
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        FileLog.Warn("Database update error - ", ex);
                    }
                }

            }
            catch (Exception ex)
            {
                FileLog.Fatal("Failed during database update.", ex);
            }
            finally
            {
                conn.Close();
            }
        }

        private void updateTo2_0_4()
        {
            var query = new[] {
                "ALTER TABLE [ProductOperationTime] ADD [OperationID] INTEGER",
                "ALTER TABLE [ProductOperationTime] ADD [OperationPass] SMALLINT",
                "ALTER TABLE [ProductOperationTime] ADD [Rate] NUMERIC(10,2) DEFAULT 0 NOT NULL",
                "ALTER TABLE [ProductOperationTime] DROP COLUMN [WorkOrderField1]",
                "DROP INDEX ProductOperationTime.IDX_ProductOperationTime_Key",
                "ALTER TABLE [ProductOperationTime] DROP COLUMN [WorkOrderField2]",
                "CREATE INDEX [IDX_ProductOperationTime_OperationPass] ON [ProductOperationTime] ([OperationPass])",
                "CREATE INDEX [IDX_ProductOperationTime_4_FK] ON [ProductOperationTime] ([OperationID])",

                "ALTER TABLE [OperatorAuth] ADD [OperationID] INTEGER",
                "CREATE  INDEX [IDX_OperatorAuth_X_FK] ON [OperatorAuth] ([OperationID])",
                "DROP INDEX OperatorAuth.IDX_OperatorAuth_WorkOrderField1",
                "ALTER TABLE [OperatorAuth] DROP COLUMN [WorkOrderField1]",

                "ALTER TABLE [ReasonCode] ADD [ShopClockEvent] SMALLINT NOT NULL DEFAULT 0",
                "DROP INDEX ReasonCode.IDX_ReasonCode_Category",
                "ALTER TABLE ReasonCode ALTER COLUMN Category DROP DEFAULT",
                "ALTER TABLE [ReasonCode] ALTER COLUMN [Category] SMALLINT NOT NULL",
                "CREATE INDEX [IDX_ReasonCode_Category] ON [ReasonCode] ([Category])",
                "ALTER TABLE ReasonCode ADD CONSTRAINT [DEF_ReasonCode_Category] DEFAULT 0 FOR Category",
                
                "ALTER TABLE [ProductionPlan] ADD [OperationID] INTEGER",
                "ALTER TABLE [ProductionPlan] ADD [OperationPass] SMALLINT",
                "ALTER TABLE [ProductionPlan] ADD [StationID] INTEGER",
                "CREATE INDEX [IDX_ProductionPlan_6_FK] ON [ProductionPlan] ([OperationID])",
                "CREATE INDEX [IDX_ProductionPlan_OperationPass] ON [ProductionPlan] ([OperationPass])",

                "ALTER TABLE [ProductionHistory] ADD [OperatorID] INTEGER",
                "CREATE  INDEX [IDX_ProductionHistory_6_FK] ON [ProductionHistory] ([OperatorID])",

                "CREATE TABLE [Operation] ([OperationID] INTEGER NOT NULL,[Description] NVARCHAR(50) NOT NULL, [Enabled] BIT CONSTRAINT [DEF_Operation_Enabled] DEFAULT 1 NOT NULL, [TimeStamp] BINARY(8) NOT NULL, PRIMARY KEY ([OperationID]))",
                "CREATE UNIQUE  INDEX [IDX_Operation_PK] ON [Operation] ([OperationID])",

                "INSERT INTO DatabaseVersion(VersionNumber, CreatedDate) VALUES('2.0.4', GetDate())"
            };

            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                foreach (string s in query)
                {
                    try
                    {
                        CreateCommand(s);
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        FileLog.Warn("Database update error - ", ex);
                    }
                }

            }
            catch (Exception ex)
            {
                FileLog.Fatal("Failed during database update.", ex);
            }
            finally
            {
                conn.Close();
            }
        }

        private void fixCellOperator()
        {
            var query = new[] {
                "DROP INDEX Cell.IDX_Cell_2_FK",
                "ALTER TABLE [Cell] DROP CONSTRAINT Operator_Cell",
                "ALTER TABLE [Cell] DROP COLUMN [OperatorID]"
            };

            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                foreach (string s in query)
                {
                    try
                    {
                        CreateCommand(s);
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        FileLog.Warn("Database update error - ", ex);
                    }
                }

            }
            catch (Exception ex)
            {
                FileLog.Fatal("Failed during database update.", ex);
            }
            finally
            {
                conn.Close();
            }
        }

        public bool VersionDatabaseExists()
        {
            bool ret = false;
            string selectQuery = "";
            selectQuery = "SELECT * FROM DatabaseVersion";
            try
            {
                CreateCommand(selectQuery);

                if (conn.State != ConnectionState.Open)
                    conn.Open();

                DbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    ret = true;
                    break;
                }
                reader.Close();
            }
            catch
            {
                ret = false;
            }
            CloseConnection();
            return ret;

        }

        private void CreateCommand(string sql)
        {
            if (conn == null)
            {
                string cfg = ConfigFileHelper.ReadConnectionStringFromConfig("ConnectionString.SQL Server (SqlClient)");
                conn = new SqlCeConnection(cfg);
            }
            command = new SqlCeCommand(sql, conn);
        }

        private void CreateDbDataAdapter(SqlCeCommand cmd)
        {
            adapter = new SqlCeDataAdapter(cmd);
        }

        private void CloseConnection()
        {
            if (conn != null)
            {
                conn.Close();
            }
        }


        public int CompareDBVersion(string appVersion, string dbVersion)
        {
            /// Return -1 if DBVersion is greater than hardcoded version (Application should exit)
            /// Return  0 if DBVersion is equal to hardcoded version (Continue as normal)
            /// Return  1 if DBVersion is less than hardcoded version (Upgrade database then continue)

            if (String.IsNullOrEmpty(dbVersion))
                return 1;
            int ret = 0;
            string[] appVer = appVersion.Split('.');
            string[] dbVer = dbVersion.Split('.');
            if (appVer.Length != dbVer.Length)
            {
                ret = -1;
            }
            else
            {
                if (Convert.ToInt32(dbVer[0]) > Convert.ToInt32(appVer[0]))
                {
                    ret = -1;
                    return ret;
                }
                else if (Convert.ToInt32(dbVer[0]) == Convert.ToInt32(appVer[0]))
                {
                    if (Convert.ToInt32(dbVer[1]) > Convert.ToInt32(appVer[1]))
                    {
                        ret = -1;
                        return ret;
                    }
                    else if (Convert.ToInt32(dbVer[1]) == Convert.ToInt32(appVer[1]))
                    {
                        if (Convert.ToInt32(dbVer[2]) > Convert.ToInt32(appVer[2]))
                        {
                            ret = -1;
                            return ret;
                        }
                        else if (Convert.ToInt32(dbVer[2]) == Convert.ToInt32(appVer[2]))
                        {
                            ret = 0;
                        }
                        else
                        {
                            ret = 1;
                        }
                    }
                    else
                    {
                        ret = 1;
                    }
                }
                else
                {
                    ret = 1;
                }
            }

            return ret;
        }

    }
}